const query = require('./query')

const queryGoods = async (page, keyword, categoryId, isPromo, isStock, key, isPriceDown) => {
  const sql = `select a.goods_id,a.goods_name,b.goods_price
  from goods a
  join goodsInfo b
  join category_one c
  join category_two d
  ${key === '3' ? "join (select 'goods_id','goos_price' from goodsInfo order by goos_price asc) g" : ''}
  where a.goods_status = 1
  and a.goods_id = b.goods_id
  ${keyword === '全部商品'
      ? ''
      : `and a.categoryTwo_id = d.categoryTwo_id 
        and d.categoryOne_id = c.categoryOne_id
        and c.categoryOne_name like '%${keyword}%'`
    }
  ${categoryId ? categoryId === '0' ? '' : `and a.categoryTwo_id = ${categoryId}` : ''}
  ${isPromo === 'true' ? 'and b.goods_discount is not null' : ''}
  ${isStock === 'true' ? 'and a.goods_number > 0' : ''}
  ${key === '3' ? 'and b.goods_price is not null' : ''}
  group by a.goods_id
  ${key === '0'
      ? ''
      : key === '1'
        ? 'order by a.goods_id desc'
        : key === '2'
          ? 'order by a.goods_sale desc'
          : key === '3'
            ? `order by b.goods_price ${isPriceDown === 'true' ? 'desc' : 'asc'}`
            : ''
    }
  limit ${(page - 1) * 20, 20}
`
  try {
    const result = await query(sql)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryGoodsByCategoryOneId = async (categoryOne_id) => {
  const sql = `select a.goods_id, a.goods_name as title, c.goods_image_path as imgUrl
  from goods a
  join goodsInfo b
  join goods_image c
  join category_two d
  where a.goods_status = 1
  and b.goods_price is not null
  and a.categoryTwo_id = d.categoryTwo_id
  and d.categoryOne_id = ?
  and a.goods_id = b.goods_id
  and b.goods_image_id = c.goods_image_id
  group by a.goods_id
  order by a.goods_id desc  
  limit 6`
  try {
    const result = await query(sql, categoryOne_id)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

const queryGoodsByCategotyTwoId = async (categoryTwo_id) => {
  const sql = `select a.goods_id, a.goods_name, c.goods_image_path
  from goods a
  join goodsInfo b
  join goods_image c
  where a.goods_status = 1
  and b.goods_price is not null
  and a.categoryTwo_id = ?
  and a.goods_id = b.goods_id
  and b.goods_image_id = c.goods_image_id
  group by a.goods_id
  order by a.goods_id desc
  limit 6`
  try {
    const result = await query(sql, categoryTwo_id)
    return result
  } catch (error) {
    return Promise.reject(error)
  }
}

module.exports = {
  queryGoods,
  queryGoodsByCategotyTwoId,
  queryGoodsByCategoryOneId
}
